package cs4280.ass2.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;

import cs4280.ass2.entity.House;
import cs4280.ass2.entity.HouseSeat;

public class HouseDao extends BaseDao {
	private static final Logger logger = Logger.getLogger(HouseDao.class);
	private static final String _SELECT_BY_ID = "select h.*,s.row,s.col from HOUSE h join HOUSE_SEAT s on h.id = s.house_id where h.ID = ?";
	private static final String _SELECT_ALL = "select * from HOUSE order by NAME asc";

	private static HouseDao houseDao = null;

	public static HouseDao getInstance() {
		if (houseDao == null) {
			houseDao = new HouseDao();
		}
		return houseDao;
	}

	public House retrieveHouse(long id) {
		House house = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_BY_ID,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, id);
			result = statement.executeQuery();
			while (result.next()) {
				if (house == null) {
					house = new House();
					house.setId(result.getLong("id"));
					house.setName(result.getString("name"));
					house.setAddress(result.getString("address"));
					house.setCreateDate(toUtilDate(result
							.getTimestamp("create_date")));
					house.setCreateUser(result.getLong("create_user"));
					house.setModifyDate(toUtilDate(result
							.getTimestamp("modify_date")));
					house.setModifyUser(result.getLong("modify_user"));
				}
				house.getSeatLocationList().add(
						new HouseSeat(result.getString("row"), result
								.getInt("col")));
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveHouse ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return house;
	}

	public List<House> retrieveAllHouse() {
		List<House> houseList = new ArrayList<House>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL);

			House house;
			while (result.next()) {
				// reuse retrieveHouse() function
				house = this.retrieveHouse(result.getLong("id"));
				houseList.add(house);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveHouse ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return houseList;
	}

}
